-- @author lixiyong01
-- @date 2024.03.26
-- 队列运行检查， 将spark队列的中的非小时级任务进行调整

use hdp_lbg_supin_zplisting;

insert overwrite table app_zp_rock_husky_queue_check partition(dt = '${dateSuffix}')

select a.user_oa, a.task_id, a.task_name, a.hadoop_queue_name, b.inst_cnt
from (
    select task_id, task_name, hadoop_queue_name, user_oa, table_output
    from hdp_lbg_supin_zplisting.dwd_zp_rock_task_relation
    where dt='${#date(0,0,-1):yyyyMMdd#}' and status = 1 --在线状态
        and hadoop_queue_name in ('root.spark.hdp_lbg_supin.spark', 'root.spark.hdp_lbg_zhaopin.spark')
    group by task_id, task_name, hadoop_queue_name, user_oa, table_output
) a
left join (
    select task_id, task_name, count(inst_id) as inst_cnt
    from hdp_lbg_supin_zplisting.dwd_zp_rock_task_inst
    where dt='${#date(0,0,-1):yyyyMMdd#}' and type=1 --1-每天自动运行
    group by task_id, task_name
) b on a.task_id = b.task_id
where b.inst_cnt < 3 --设定3次以下不可走实时


